package cn.telling.sellercentre.dao.impl;


import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import cn.telling.common.pager.PageVo;
import cn.telling.common.pager.Pager;
import cn.telling.common.util.TellingConstance;
import cn.telling.product.vo.ShopProductVo;
import cn.telling.product.vo.SupplyVo;
import cn.telling.product.vo.TelMarketPubHis;
import cn.telling.sellerCenter.vo.ProductSortVo;
import cn.telling.sellerCenter.vo.QuotationVo;
import cn.telling.sellerCenter.vo.SellerBasePrice;
import cn.telling.sellerCenter.vo.ShopProductApproveVo;
import cn.telling.sellerCenter.vo.ShopProductAreaApproveVo;
import cn.telling.sellerCenter.vo.ShopProductAreaVo;
import cn.telling.sellercentre.dao.ISellerConCentreDao;
import cn.telling.utils.AutoInjection;
import cn.telling.utils.BaseUtil;
import cn.telling.utils.StringHelperTools;



/**   
 * @Description: 产品管理   产品中心数据处理层实现
 * @author yanhaiwei
 */
@Repository
public class SellerConCentreDaoImpl implements ISellerConCentreDao{
	private static Logger logger = Logger.getLogger(SellerConCentreDaoImpl.class);
	
	@Autowired
	@Qualifier("film-template")
	private JdbcTemplate jdbcTemplate;
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	/**根据用户Id获得商铺简介的信息*//*
	@Override
	public SupplyVo GetShopInfo(String userId) {
		String sql = "select introduction from supply where user_id = ?" ;
		SupplyVo supplyVo = null;
		List<SupplyVo> list = jdbcTemplate.query(sql,new Object[]{userId},new SupplyVoMapper());
		
		if(list != null && list.size() > 0){
			supplyVo = list.get(0);
		}
		return supplyVo;
	}*/
	/*private static final class SupplyVoMapper implements RowMapper<SupplyVo> {
		public SupplyVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			SupplyVo supplyVo = new SupplyVo();
			supplyVo.setINTRODUCTION(rs.getString("introduction"));		
			return supplyVo;
		}
	}*/

	/**根据用户Id修改商铺简介信息*//*
	@Override
	public int SetShopIntroduce(String userId, String shopintroduce) {
		String sql = "update supply set introduction = ?,update_date = sysDate,update_user = ? where user_id = ?";
		int count = jdbcTemplate.update(sql, new Object[]{shopintroduce,userId,userId});
		return count;
	}*/
	
	/**获得卖家中心已上架、已下架、审核中产品列表 */
	@Override
	public List<ShopProductAreaVo> getSaleListByStatus(String userId,String saleType, String status, String conditions,String productfirstSortId,String productSortid) {
		
		String sql="";
		if( (!"".equals(productfirstSortId)) &&"".equals( productSortid) ){
						
			  sql = "with productData as( " +
					 " select sp.id as shopProductId, sp.productId, pm.productName, sp.supplyId," +
					 " sa.id as supplyAreaId, sa.saleType, sa.areaId, ai.areaName, sa.customId, NVL(bi.shopName,cb.shopName) as companyName," +
					 " sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts," +
					 " sa.onShelfTime, sa.offShelfTime, sa.isShare, sa.shareCode,sa.iscontinuous,sa.is_contain_invoice as isContainInvoice,pm.saleType as productSaleType " +
					 " from shop_product sp " +
					 
					 //and pm.del_flag ='1' and s.del_flag = '1'
					 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " inner join supply s on sp.supplyId = s.id and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
					 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " + 
					 " left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " left join areaInfo ai on sa.areaId = ai.areaId " +
					 " left join buyerInfo bi on sa.customId = bi.buyerId"+
					 " left join common_buyerinfo cb  on sa.customId=cb.gbuyerid"+					
					 " where s.user_id = ? " +
					 ((!"".equals(status) && status != null) ? " and sa.status = '" + status + "' and sa.saleStatus <> '" + TellingConstance.SALESTATUS1 + "' " : " and (spa.approveStatus = '" + TellingConstance.SALESTATUS1 + "' or spa.approveStatus is null) ") +
					 ((!"".equals(saleType) && saleType != null) ? " and sa.saleType in ('" + saleType + "') " : "") + 
					 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " 
					 + ( !("".equals(productfirstSortId) &&productfirstSortId!=null) ? ("and pm.sortid in(select SORTID from product_sort where PARENTID=" + productfirstSortId+"and DEL_FLAG='1'" +")") :"" ) +
									 
					 " and lower(replace(pm.productName, ' ', '')) like ? " +
					 " group by sp.id, sp.productId, pm.productName, sp.supplyId, sa.iscontinuous,sa.id, sa.saleType, sa.areaId, ai.areaName, sa.customId, NVL(bi.shopName,cb.shopName), sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime,sa.isShare, sa.shareCode,sa.is_contain_invoice,pm.saleType" +
					 "), " +
					 "promotiondData as (" +
					 " select tpm.*,tmd.shopproductid from tm_PROMOTIONM tpm " +
					 " join tm_PROMOTIOND tmd on tpm.id=tmd.pid where  tpm.status='1' " +
					 " and ( to_char(tpm.endtime,'yyyy-mm-dd')||' '||to_char(tpm.l_endtime,'hh24:mi:ss') )  >  to_char (sysdate,'yyyy-mm-dd hh24:mi:ss' ) " +
				
					 ")"  
					 +					 
					 "select pd.shopProductId, pd.productId, pd.productName, pd.supplyId, pd.supplyAreaId," +
					 " pd.saleType, pd.areaId, pd.areaName, pd.customId, pd.companyName,pd.iscontinuous,pd.isContainInvoice,"+
					 " pd.priceRetailOnline,(nvl(pd.stockNumber,0)) stockNumber, pd.afterSale, pd.gifts," +
					 " pd.onShelfTime, pd.offShelfTime, pd.isShare, pd.shareCode," +
					 " pdd.status promotiondStatus,pd.productSaleType " +					 
					 " from productData pd " +
					 "left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +					 
					 "order by pd.productName";
			
		}else{			
		
		     sql = "with productData as( " +
				 " select sp.id as shopProductId, sp.productId, pm.productName, sp.supplyId," +
				 " sa.id as supplyAreaId, sa.saleType, sa.areaId, ai.areaName, sa.customId, NVL(bi.shopName,cb.shopName) as companyName," +
				 " sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts," +
				 " sa.onShelfTime, sa.offShelfTime, sa.isShare, sa.shareCode,sa.iscontinuous,sa.is_contain_invoice as isContainInvoice,pm.saleType as productSaleType " +
				 " from shop_product sp " +
				 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " inner join supply s on sp.supplyId = s.id and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
				 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " + 
				 " left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " left join areaInfo ai on sa.areaId = ai.areaId " +
				 " left join buyerInfo bi on sa.customId = bi.buyerId"+
				 " left join common_buyerinfo cb  on sa.customId=cb.gbuyerid"+				 
				 " where s.user_id = ? " +
				 
				 ((!"".equals(status) && status != null) ? " and sa.status = '" + status + "' and sa.saleStatus <> '" + TellingConstance.SALESTATUS1 + "' " : " and (spa.approveStatus = '" + TellingConstance.SALESTATUS1 + "' or spa.approveStatus is null) ") +
				 ((!"".equals(saleType) && saleType != null) ? " and sa.saleType in ('" + saleType + "') " : "") + 
				 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " 
				 + ( !("".equals(productSortid) &&productSortid!=null) ? ("and pm.sortid ='"+productSortid + "'") :"" ) +
								 
				 " and lower(replace(pm.productName, ' ', '')) like ? " +
				 " group by sp.id, sp.productId, pm.productName, sp.supplyId, sa.iscontinuous,sa.id, sa.saleType, sa.areaId, ai.areaName, sa.customId, NVL(bi.shopName,cb.shopName), sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime,sa.isShare, sa.shareCode,sa.is_contain_invoice,pm.saleType" +
				 "), " +				 
				 "promotiondData as (" +
				 " select tpm.*,tmd.shopproductid from tm_PROMOTIONM tpm " +
				 " join tm_PROMOTIOND tmd on tpm.id=tmd.pid where  tpm.status='1' " +
				 
				 " and ( to_char(tpm.endtime,'yyyy-mm-dd')||' '||to_char(tpm.l_endtime,'hh24:mi:ss') )  >  to_char (sysdate,'yyyy-mm-dd hh24:mi:ss' ) " +
				
				 ")"				 
				 +		
				 "select pd.shopProductId, pd.productId, pd.productName, pd.supplyId, pd.supplyAreaId," +
				 " pd.saleType, pd.areaId, pd.areaName, pd.customId, pd.companyName,pd.iscontinuous,pd.isContainInvoice,"+
				 " pd.priceRetailOnline,(nvl(pd.stockNumber,0) ) stockNumber, pd.afterSale, pd.gifts," +
				 " pd.onShelfTime, pd.offShelfTime, pd.isShare, pd.shareCode," +
				 " pdd.status promotiondStatus,pd.productSaleType" +				
				 " from productData pd " +
				 "left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +			 
				 "order by pd.productName";		 
		}		
		
	   Object[] params = new Object[]{userId, "%" + conditions.replaceAll("'", "''").replaceAll("\\s*", "").toLowerCase() + "%"};
     	params = StringHelperTools.nullToString(params);
	
	   logger.debug("获得卖家中心产品列表" +BaseUtil.logSQL(sql, params));
	
	   List<ShopProductAreaVo> shopProductAreaVoList = jdbcTemplate.query(sql, params, tempMapper);
	
	  if(shopProductAreaVoList != null && shopProductAreaVoList.size() > 0){
		    return shopProductAreaVoList;
     	}
	      return null;
	}
	/**数据转化*/
	private ParameterizedRowMapper<ShopProductAreaVo> tempMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setShopProductId(rs.getBigDecimal("shopProductId"));
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductAreaVo.setProductId(rs.getBigDecimal("productId"));
			shopProductAreaVo.setProductName(rs.getString("productName"));
			shopProductAreaVo.setSaleType(rs.getString("saleType"));
			shopProductAreaVo.setAreaId(rs.getLong("areaId"));
			shopProductAreaVo.setAreaName(rs.getString("areaName"));
			shopProductAreaVo.setCustomId(rs.getLong("customId"));
			shopProductAreaVo.setCustomName(rs.getString("companyName"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setAfterSale(rs.getString("afterSale"));
			shopProductAreaVo.setGifts(rs.getString("gifts"));
			shopProductAreaVo.setOnShelfTime(rs.getDate("onShelfTime"));
			shopProductAreaVo.setOffShelfTime(rs.getDate("offShelfTime"));
			shopProductAreaVo.setIsShare(rs.getString("isShare"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			shopProductAreaVo.setPromotiondStatus(rs.getString("promotiondStatus"));
		//	shopProductAreaVo.setPromotiondNum(rs.getBigDecimal("promotiondNum"));
		 // shopProductAreaVo.setSeckillStatus(rs.getString("seckillStatus"));
		 //	shopProductAreaVo.setSeckillNum(rs.getBigDecimal("seckillNum"));
			shopProductAreaVo.setIscontinuous(rs.getString("iscontinuous"));
			shopProductAreaVo.setIsContainInvoice(rs.getString("isContainInvoice"));
			shopProductAreaVo.setProductSaleType(rs.getString("productSaleType"));
			return shopProductAreaVo;
		}
	};

	/**获取基准价格*/
	@Override
	public SellerBasePrice getSellerBasePrice(String supplyId, String productId) {
		String sql = "select supplyId, productId, baseprice from seller_basePrice where supplyId = ? and productId = ? and del_flag = 1 ";
		Object[] params = new Object[]{supplyId,productId};
		params = StringHelperTools.nullToString(params);
		
		logger.debug("获取基准价格 ：" + BaseUtil.logSQL(sql, params));
		List<SellerBasePrice> list = jdbcTemplate.query(sql, params, tempMappers);
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	private ParameterizedRowMapper<SellerBasePrice> tempMappers = new ParameterizedRowMapper<SellerBasePrice>() {
		@Override
		public SellerBasePrice mapRow(ResultSet rs, int rowNum) throws SQLException {
			SellerBasePrice sellerBasePrice = new SellerBasePrice();
			sellerBasePrice.setSupplyId(rs.getBigDecimal("supplyId"));
			sellerBasePrice.setProductId(rs.getBigDecimal("productId"));
			sellerBasePrice.setBasePrice(rs.getBigDecimal("baseprice"));
			return sellerBasePrice;
		}
	};
	/**根据供货商产品信息提交产品审核申请*/
	@Override
	public int insertShopProductApprove(ShopProductAreaApproveVo shopProductAreaApproveVo) {
		String sql = "insert into shop_product_approve(" +
				 "id, supplyAreaId, priceBefore, priceAfter, stockBefore, stockAfter, saleStatus, " +
				 "approveStatus, expApproveTime, del_flag, add_date, add_user, remark) " +
				 "values(?, ?, ?, ?, ?, ?, ?, '1', SYSDATE + 1, '3', SYSDATE, ?, ?)";// approveStatus='3' 申请下架时自动审核通过

		List<Object[]> paramList = new ArrayList<Object[]>();
		List<ShopProductAreaVo> shopProductAreaVoList = shopProductAreaApproveVo.getShopProductAreaVoList();
		for (int i = 0; i < shopProductAreaVoList.size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductAreaVoList.get(i);
			
			Object[] parmas = new Object[9];
			parmas[0] = shopProductAreaApproveVo.getId()[i];
			parmas[1] = shopProductAreaVo.getSupplyAreaId();
			parmas[2] = shopProductAreaVo.getPriceRetailOnline();
			parmas[3] = shopProductAreaVo.getApprovePrice();
			parmas[4] = shopProductAreaVo.getStockNumber();
			parmas[5] = shopProductAreaVo.getApproveStockNum();
			parmas[6] = shopProductAreaVo.getSaleStatus();
			parmas[7] = shopProductAreaVo.getAddUser();
			parmas[8] = shopProductAreaVo.getRemark();
			
			paramList.add(parmas);
			logger.debug("供货商产品信息提交产品审核申请" + BaseUtil.logSQL(sql, parmas));
		}
		
		int[] count = jdbcTemplate.batchUpdate(sql, paramList);
		return count.length;
	}
	
	/**根据货商产品信息提交产品审核申请 更新供应商产品区域销售状态*/
	@Override
	public int updateSupplyAreaSaleStatus(ShopProductAreaApproveVo shopProductAreaApproveVo) {
        List<ShopProductAreaVo> shopProductAreaVoList = shopProductAreaApproveVo.getShopProductAreaVoList();
        
        if(shopProductAreaVoList == null || shopProductAreaVoList.isEmpty()) {
            return 0;
        }
	
		String sql = "";//审核通过：saleStatus = '3'，下架状态： status='2'
		String[] bSql = new String[shopProductAreaVoList.size()];
		
		// 取出一条记录查出saleStauts，看是申请下架还是申请上架，如果是申请上架，则还需要修改价格、库存、赠品、是否带发票
		String saleStatus = shopProductAreaVoList.get(0).getSaleStatus();
		if("1".equals(saleStatus)) { //上架
            for (int i = 0; i < shopProductAreaVoList.size(); i++) {
                ShopProductAreaVo shopProductAreaVo = shopProductAreaVoList.get(i);
                List<Object> parmasList=new ArrayList<Object>();
                
                parmasList.add(shopProductAreaVo.getSaleStatus());
                parmasList.add(shopProductAreaVo.getAddUser());
                parmasList.add(shopProductAreaVo.getApprovePrice());
                parmasList.add(shopProductAreaVo.getApproveStockNum());
                parmasList.add(shopProductAreaVo.getApproveStockNum());  
                String parmas = StringHelperTools.nvl(shopProductAreaVo.getApproveGifts());   /**礼品参数*/
                
                // 若是非定向发布，则根据产品指定供应商ID批量修改
                if(shopProductAreaVo.getSaleType().equals(TellingConstance.SALETYPE2)) {
                	
                    sql = "update supply_area0 set saleStatus = '3', status=?, update_user = ?, PRICERETAILONLINE=?, STOCKNUMBER=?, OVERPLUSNUMBER=?,";

                	if(!"".equals(parmas)){
                	   sql=sql+" GIFTS=?,";
                       parmasList.add(parmas);

                	}
                	sql=sql+ "IS_CONTAIN_INVOICE=? where id = ? and DEL_FLAG='1' and STATUS='2'";
                    parmasList.add(shopProductAreaVo.getApproveIsContainInvoice());
                    parmasList.add(shopProductAreaVo.getSupplyAreaId());

                } else {
                	
                	sql = "update supply_area0 set saleStatus = '3', status=?, update_user = ?, PRICERETAILONLINE=?, STOCKNUMBER=?, OVERPLUSNUMBER=?,";
                 	if(!"".equals(parmas)){
                 	   sql=sql+" GIFTS=?,";
                        parmasList.add(parmas);

                 	}
                   	sql=sql+ "IS_CONTAIN_INVOICE=? where id = ? and DEL_FLAG='1' and STATUS='2'";
                   	
                    parmasList.add(shopProductAreaVo.getApproveIsContainInvoice());
                    parmasList.add(shopProductAreaVo.getSupplyAreaId());
                }
                bSql[i] = BaseUtil.logSQL(sql, parmasList.toArray());
                logger.debug("更新供应商产品区域销售状态-上架" + BaseUtil.logSQL(sql, parmasList.toArray()) );
            }
		} else { //下架
            for (int i = 0; i < shopProductAreaVoList.size(); i++) {
                ShopProductAreaVo shopProductAreaVo = shopProductAreaVoList.get(i);
                Object[] parmas = new Object[3];
                parmas[0] = shopProductAreaVo.getSaleStatus();
                parmas[1] = shopProductAreaVo.getAddUser();
                // 若是非定向发布，则根据产品指定供应商ID批量修改
                if(shopProductAreaVo.getSaleType().equals(TellingConstance.SALETYPE2)) {
                    sql = "update supply_area0 set saleStatus = '3', status=?, update_user = ? where SUPPLYPRODUCTID = ? and DEL_FLAG='1' and STATUS='1'";//审核通过：saleStatus = '3'，下架状态： status='2'
                    parmas[2] = shopProductAreaVo.getShopProductId();
                } else {
                    sql = "update supply_area0 set saleStatus = '3', status=?, update_user = ? where id = ?";//审核通过：saleStatus = '3'，下架状态： status='2'
                    parmas[2] = shopProductAreaVo.getSupplyAreaId();
                }
                bSql[i] = BaseUtil.logSQL(sql, parmas);
                logger.debug("更新供应商产品区域销售状态-下架" + BaseUtil.logSQL(sql, parmas) );
            }
		}
        int[] count = jdbcTemplate.batchUpdate(bSql);
        return count.length;
	}
	/**
	 * 修改通信市场产品发布历史 截止日期
	 */
	@Override
	public int updateTelMarketPubHis(String productId, String supplyAreaId) {
		String sql = "update telMarket_pub_his set deadlineTime = sysdate where product_id = ? and supplyAreaId = ? and deadlineTime is null";
		Object[] parmas = new Object[]{productId,supplyAreaId};
		parmas = StringHelperTools.nullToString(parmas);
		
		int count = jdbcTemplate.update(sql, parmas);
		return count;
	}
	/**入库基准价格*/
	@Override
	public int insertSellerBsePrice(SellerBasePrice sellerBasePrice) {
		String sql = "insert into seller_basePrice(id, supplyId, productId, baseprice, add_date, add_user, update_date, update_user, del_flag) " +
					 " values(?,?,?,?,sysdate,?,sysdate,?,?)";
		Object[] params = new Object[]{sellerBasePrice.getId(),sellerBasePrice.getSupplyId(), 
						sellerBasePrice.getProductId(),sellerBasePrice.getBasePrice(),
						sellerBasePrice.getAdd_user(),sellerBasePrice.getUpdate_user(),
						TellingConstance.DEL_FLAG_1
					};
		params = StringHelperTools.nullToString(params);
		logger.debug("入库基准价格 ：" + BaseUtil.logSQL(sql, params));
		int count = jdbcTemplate.update(sql, params);
		return count;
	}
	/**更新基准价格*/
	@Override
	public int updateSellerBasePrice(BigDecimal basePrice,BigDecimal userId,String supplyId, String productId) {
		String sql = "update seller_basePrice set basePrice = ?, update_date = sysdate, update_user = ? where supplyId = ? and productId = ? ";
		Object[] params = new Object[]{basePrice, userId,supplyId,productId};
		params = StringHelperTools.nullToString(params);
		int count = jdbcTemplate.update(sql, params);
		logger.debug("更新基准价格 ：" + BaseUtil.logSQL(sql, params));
		return count;
	}
	/**
	 * 记录单个通信市场产品发布历史
	 */
	@Override
	public int insertTelMarketPubHis(TelMarketPubHis telMarketPubHis) {
		String sql = "insert into telMarket_pub_his(product_id,supplyId,pulishiTime,supplyAreaId,price,stockNum,add_date,add_user,update_date,update_user) " + 
				 "values(?,?,sysdate,?,?,?,sysdate,?,sysdate,?) ";
		Object[] parmas = new Object[]{telMarketPubHis.getProduct_id(),telMarketPubHis.getSupplyId(),
					telMarketPubHis.getSupplyAreaId(),telMarketPubHis.getPrice(),
					telMarketPubHis.getStockNum(),telMarketPubHis.getAdd_user(),
					telMarketPubHis.getUpdate_user()
				};
		parmas = StringHelperTools.nullToString(parmas);
		int count = jdbcTemplate.update(sql, parmas);
		return count;
	}
	/**根据共享编码查询出共享产品列表  供共享使用*/
	@Override
	public List<ShopProductAreaVo> getSupplyAreaByShareCode(String shareCode) {
		String sql = "select sa.id as supplyAreaId, sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.shareCode from supply_area0 sa where sa.shareCode = ?";
		Object[] params = new Object[]{shareCode};
		params = StringHelperTools.nullToString(params);
		
		logger.debug("根据共享编码查询出共享产品列表" + BaseUtil.logSQL(sql, params));
		List<ShopProductAreaVo> list = jdbcTemplate.query(sql, params, supplyAreaMapper);
		if(list != null && list.size() > 0){
			return list;
		}
		return null;
	}
	/**数据转化*/
	private ParameterizedRowMapper<ShopProductAreaVo> supplyAreaMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			return shopProductAreaVo;
		}
	};
	/**根据supplyAreaId 查询出供货商产品区域*/
	@Override
	public ShopProductAreaVo getSupplyAreaById(String supplyAreaId) {
		String sql = "select sa.pk_stordoc,pm.productName,sa.id as supplyAreaId, sp.id as shopProductId, sp.productId, sp.supplyId, sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.saleType, sa.isShare, sa.shareCode " +
					 "from supply_area0 sa " + 
					 "left join shop_product sp on sa.supplyproductId = sp.id and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "'  " +
					 "inner join product_main pm on sp.productid=pm.productid and pm.del_flag='1'"+
					 "where sa.id = ? and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' ";
		Object[] params = new Object[]{ supplyAreaId };
		params = StringHelperTools.nullToString(params);
		
		logger.debug("根据supplyAreaId 查询出供货商产品区域" + BaseUtil.logSQL(sql, params));
		List<ShopProductAreaVo> list = jdbcTemplate.query(sql, params, rowMapper);
		
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<ShopProductAreaVo> rowMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setShopProductId(rs.getBigDecimal("shopProductId"));
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductAreaVo.setProductId(rs.getBigDecimal("productId"));
			shopProductAreaVo.setSaleType(rs.getString("saleType"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setIsShare(rs.getString("isShare"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			shopProductAreaVo.setProductName(rs.getString("productName"));
			shopProductAreaVo.setPk_stordoc(rs.getString("pk_stordoc"));
			return shopProductAreaVo;
		}
	};
	/**根据供应商产品信息更新供应商产品继续竞价状态*/
	@Override
	public int UpdateContinuous(ShopProductAreaVo shopProductAreaVo) {
		String updateSQL = " update supply_area0 set isContinuous = ? where id = ? ";
		Object[] parmas = new Object[2];
		parmas[0] = shopProductAreaVo.isContinuous() ? "0" : "1";
		parmas[1] = shopProductAreaVo.getId();
		
		int i = jdbcTemplate.update(updateSQL.toString(), parmas);
		return i;
	}
	/**批量更新基准价格*/
	@Override
	public int updateSellerBasePriceBatch(List<SellerBasePrice> sellerBasePriceList) {
		String sql = "update seller_basePrice set basePrice = ?, update_date = sysdate, update_user = ? where supplyId = ? and productId = ? ";
		List<Object[]> paramsList = new ArrayList<Object[]>();
		for (SellerBasePrice sellerBasePrice : sellerBasePriceList) {
			Object[] params = new Object[]{sellerBasePrice.getBasePrice(), sellerBasePrice.getAdd_user(),
											sellerBasePrice.getSupplyId(),sellerBasePrice.getProductId()
										};
			params = StringHelperTools.nullToString(params);
			
			paramsList.add(params);
		}
		logger.debug("批量更新基准价格 ：" + BaseUtil.logSQL(sql, new Object[]{}));
		
		int count[] = jdbcTemplate.batchUpdate(sql,paramsList);
		
		return count.length;
	}
	/**
	 * 批量记录通信市场产品发布历史
	 */
	@Override
	public int[] insertTelMarketPubHis(final List<TelMarketPubHis> telMarketPubHisList) {
		final String sql = "insert into telMarket_pub_his(product_id,supplyId,pulishiTime,supplyAreaId,price,stockNum,add_date,add_user,update_date,update_user) " + 
					 "values(?,?,sysdate,?,?,?,sysdate,?,sysdate,?) ";
		
		int[] count = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				TelMarketPubHis telMarketPubHis = telMarketPubHisList.get(i);
				ps.setBigDecimal(1, telMarketPubHis.getProduct_id());
				ps.setBigDecimal(2, telMarketPubHis.getSupplyId());
				ps.setBigDecimal(3, telMarketPubHis.getSupplyAreaId());
				ps.setBigDecimal(4, telMarketPubHis.getPrice());
				ps.setBigDecimal(5, telMarketPubHis.getStockNum());
				ps.setBigDecimal(6, telMarketPubHis.getAdd_user());
				ps.setBigDecimal(7, telMarketPubHis.getUpdate_user());
				
				Object[] object = new Object[]{ telMarketPubHis.getProduct_id(),telMarketPubHis.getSupplyId(),
						telMarketPubHis.getSupplyAreaId(),telMarketPubHis.getPrice(),telMarketPubHis.getStockNum(),
						telMarketPubHis.getAdd_user(),telMarketPubHis.getUpdate_user()
					 };
				object = StringHelperTools.nullToString(object);
				logger.debug("更新telMarket_pub_his表的数据:"+BaseUtil.logSQL(sql,object));
			}
			
			@Override
			public int getBatchSize() {
				return telMarketPubHisList.size();
			}
		});
		
		return count;
	}
	/**批量更新通信市场产品发布历史 截止日期*/
	@Override
	public int updateTelMarketPubHisBatch(List<String> productIdList, List<String> supplyAreaIdList) {
		String sql = "update telMarket_pub_his set deadlineTime = sysdate where product_id = ? and supplyAreaId = ? and deadlineTime is null";
		List<Object[]> paramsList = new ArrayList<Object[]>();
		for (int i = 0; i < supplyAreaIdList.size(); i++) {
			Object[] parmas = new Object[]{productIdList.get(i), supplyAreaIdList.get(i)};
			parmas = StringHelperTools.nullToString(parmas);
			
			paramsList.add(parmas);
		}
		
		logger.debug("批量更新通讯市场产品发布历史截止日期:" +BaseUtil.logSQL(sql, new Object[]{}));
		
		int count[] = jdbcTemplate.batchUpdate(sql,paramsList);
		
		return count.length;
	}
	/**根据用户Id获得供应商类型    空   外部供应商  01   天音供应商     02 03厂家供应商*/
	@Override
	public String GetSupplyType(String userId) {
		String sql = "select supply_type from supply where user_id = ?" ;
		Object[] params = new Object[]{ userId };
		params = StringHelperTools.nullToString(params);
		
		List<String> list = jdbcTemplate.query(sql, params, new RowMapper<String>(){
			public String mapRow(ResultSet rs,int rowNum) throws SQLException{
				String supplyType = rs.getString("supply_type");
				return supplyType;
			}
		});
		
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return "";
	}
	/**根据用户Id、销售类型(定向、非定向)、上下架状态、型号名称   获得供货商产品总数*/
	@Override
	public int GetShopProductCount(String userId, String saleType, String status,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		String sql = " select count(1) " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" where a.id not in (select distinct bsp.supplyproductid from bidding_sp bsp " +
				" inner join biddingm bm on bm.id=bsp.pid and bm.statue in ('1','2') " +
				" where bsp.product_statu in ('1','2','3','4','6') and bsp.supplyproductid is not null) " + 
				" and d.user_id = '" + userId + "'" +
				" and EXISTS (select 1 from supply_area0 e where a.id = e.supplyProductId" +
				" and e.del_flag = '1' and e.isValId = '01' and e.status = '" + status + "' and e.saleStatus <> '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" ) " +
				" and a.del_flag = '1' and a.isValId = '01' ";
		
		if(modelName != null && !"".equals(modelName)){
			sql += " and pm.modelName like ? ";
			conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
		}
		
		Object[] params = conditionList.toArray();
		
		return jdbcTemplate.queryForInt(sql,params);
	}
	/**根据用户Id、销售类型(定向、非定向)、上下架状态、型号名称  分页获得供货商产品列表（分页）*/
	@Override
	public List<ShopProductVo> GetShopProductList(String userId, String saleType, String status, PageVo pageVo,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		
		String sql = " select a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1,max(e.saleType) as saleMode " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" inner join supply_area0 e on a.id = e.supplyProductId and e.del_flag = '1' and e.isValId = '01' " +
				" where a.id not in (select distinct bsp.supplyproductid from bidding_sp bsp "+
	            " inner join biddingm bm on bm.id=bsp.pid and bm.statue in ('1','2') "+
	            " where bsp.product_statu in ('1','2','3','4','6') and bsp.supplyproductid is not null) "+
	            " and d.user_id = '" + userId + "'" +
				" and e.status = '" + status + "' and e.saleStatus <> '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" and a.del_flag = '1' and a.isValId = '01' ";
				
				if(modelName != null && !"".equals(modelName)){
					sql += " and pm.modelName like ? ";
					conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
				}
				
				sql += " group by a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1 " +
					   " order by a.id desc ";
		
		String execSql = Pager.getPageDatas(pageVo, sql);
		Object[] params = conditionList.toArray();
		
		List<ShopProductVo> list = (List<ShopProductVo>)jdbcTemplate.query(execSql, params, new RowMapper<ShopProductVo>(){
			public ShopProductVo mapRow(ResultSet rs,int i) throws SQLException{
				ShopProductVo shopProductVo = new ShopProductVo();
				shopProductVo.setId(rs.getBigDecimal("id"));
				shopProductVo.setProductId(rs.getBigDecimal("productId"));
				shopProductVo.setShopId(rs.getString("shopId"));
				shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
				shopProductVo.setSaleMode(rs.getString("saleMode"));
				shopProductVo.setProductName(rs.getString("productName"));
				shopProductVo.setProductPic(rs.getString("picturePath1"));
				return shopProductVo;
			}
		});
		return list;
	}
	/**根据用户Id、销售类型(定向、非定向)、型号名称   获得审核中的供货商产品总数*/
	@Override
	public int GetApproveShopProductCount(String userId, String saleType,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		
		String sql = " select count(1) " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" where d.user_id = '" + userId + "'" +
				" and EXISTS (select 1 from supply_area0 e where a.id = e.supplyProductId" +
				" and e.del_flag = '1' and e.isValId = '01' and e.saleStatus = '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" ) " +
				" and a.del_flag = '1' and a.isValId = '01' ";
		
		if(modelName != null && !"".equals(modelName)){
			sql += " and pm.modelName like ? ";
			conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
		}
		
		Object[] params = conditionList.toArray();
		return jdbcTemplate.queryForInt(sql,params);
	}
	/**根据用户Id、销售类型(定向、非定向)、型号名称  分页获得审核中的供货商产品列表（分页）*/
	@Override
	public List<ShopProductVo> GetApproveShopProductList(String userId, String saleType, PageVo pageVo,String modelName) {
		List<String> conditionList = new ArrayList<String>();
		
		String sql = " select a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1,max(e.saleType) as saleMode " +
				" from shop_product a " +
				" inner join product_main b on a.productId = b.productId and b.del_flag = '1' " +
				" left  join product_pic_relation ppr on b.productId = ppr.product_id " +
				" left  join product_picture c on ppr.pictureId = c.pictureId and c.del_flag = '1' " +
				" left  join product_model pm on b.productPattern = pm.modelId " +
				" inner join supply d on a.supplyId = d.id and d.del_flag = '1' " +
				" inner join supply_area0 e on a.id = e.supplyProductId and e.del_flag = '1' and e.isValId = '01' " +
				" where d.user_id = '" + userId + "'" +
				" and e.saleStatus = '1' " +
				(saleType == null ? "" : " and e.saleType in ('" + saleType + "') ") +
				" and a.del_flag = '1' and a.isValId = '01' ";
				
				if(modelName != null && !"".equals(modelName)){
					sql += " and pm.modelName like ? ";
					conditionList.add("%"+ StringHelperTools.nvl(modelName) + "%");
				}
				
				sql += " group by a.id, a.productId,a.shopId,a.supplyId,b.productName,c.picturePath1 " +
					   " order by a.id desc ";
		
		String execSql = Pager.getPageDatas(pageVo, sql);
		Object[] params = conditionList.toArray();
		
		List<ShopProductVo> list = (List<ShopProductVo>)jdbcTemplate.query(execSql, params, new RowMapper<ShopProductVo>(){
			public ShopProductVo mapRow(ResultSet rs,int i) throws SQLException{
				ShopProductVo shopProductVo = new ShopProductVo();
				shopProductVo.setId(rs.getBigDecimal("id"));
				shopProductVo.setProductId(rs.getBigDecimal("productId"));
				shopProductVo.setShopId(rs.getString("shopId"));
				shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
				shopProductVo.setSaleMode(rs.getString("saleMode"));
				shopProductVo.setProductName(rs.getString("productName"));
				shopProductVo.setProductPic(rs.getString("picturePath1"));
				return shopProductVo;
			}
		});
		return list;
	}
	/**根据供货商产品Id、销售状态(1 审核中、2 审核未通过、3  审核通过)、上下架状态  获得该供货商产品的指定状态的发布信息*/
	@Override
	public List<ShopProductAreaVo> GetShopProductAreaList(long shopProductId, String saleStatus, String status) {
		
		String sql = " select sa.id, sa.areaId, ai.areaName, sa.isContinuous," +
				" sa.priceRetailOnline, spa.priceAfter, sa.overPlusNumber as stockNumber, spa.stockAfter," +
				" sa.customId, NVL(bi.companyName,bi.shopName) as companyName," +
				" sa.saleType, sa.status, spa.saleStatus," +
				" sa.onShelfTime, sa.offShelfTime, spa.expApproveTime, spa.approveTime," +
				" spa.approveUser,spa.remark," +
				" sa.isShare, sa.shareCode " +
				" from supply_area0 sa " +
				" left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag <> '0' and (spa.approveStatus = '1' or spa.approveStatus is null) " +
				" left join areaInfo ai on sa.areaId = ai.areaId" +
				" left join buyerInfo bi on sa.customId = bi.buyerId and bi.delflag <> '0' " +
				" where sa.supplyProductId = " + shopProductId +
				("0".equals(saleStatus) ? " and sa.saleStatus <> '1' and sa.status = '" + status + "' " : " and sa.saleStatus = '1' ") +
				" and sa.del_flag = '1' and sa.isValId = '01' " +
				" order by sa.id ";
		return jdbcTemplate.query(sql, new ShopProductAreaVoMapper());
	}

	/**产品区域数据转换用*/
	private static final class ShopProductAreaVoMapper implements RowMapper<ShopProductAreaVo> {

		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setId(rs.getLong("id"));
			shopProductAreaVo.setAreaId(rs.getLong("areaId"));
			shopProductAreaVo.setAreaName(rs.getString("areaName"));
			shopProductAreaVo.setContinuous(rs.getBoolean("isContinuous"));
			shopProductAreaVo.setPrice(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setApprovePrice(rs.getBigDecimal("priceAfter"));
			shopProductAreaVo.setStockNum(rs.getLong("stockNumber"));
			shopProductAreaVo.setApproveStockNum(rs.getLong("stockAfter"));
			shopProductAreaVo.setCustomId(rs.getLong("customId"));
			shopProductAreaVo.setCustomName(rs.getString("companyName"));
			shopProductAreaVo.setSaleType(rs.getString("saleType"));
			shopProductAreaVo.setProductStatus(rs.getString("status"));
			shopProductAreaVo.setSaleStatus(rs.getString("saleStatus"));
			shopProductAreaVo.setOnShelfTime(rs.getDate("onShelfTime"));
			shopProductAreaVo.setOffShelfTime(rs.getDate("offShelfTime"));
			shopProductAreaVo.setExpApproveTime(rs.getDate("expApproveTime"));
			shopProductAreaVo.setApproveTime(rs.getDate("approveTime"));
			shopProductAreaVo.setApproveUser(rs.getLong("approveUser"));
			shopProductAreaVo.setRemark(rs.getString("remark"));
			shopProductAreaVo.setIsShare(rs.getString("isShare"));
			shopProductAreaVo.setShareCode(rs.getString("shareCode"));
			return shopProductAreaVo;
		}
	}
	/**根据供货商产品Id、用户Id 获得供货商产品信息*/
	@Override
	public ShopProductVo GetShopProduct(long shopProductId, String userId) {
		String sql = " select sp.id, sp.productId,sp.shopId,'' as status,sp.supplyId,pm.productName,pp.picturePath1,RTRIM(sp.saleMode) as saleMode " +
				" from shop_product sp " +
				" inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '1' " +
				" left  join product_pic_relation ppr on pm.productId = ppr.product_id " +
				" left  join product_picture pp on ppr.pictureId = pp.pictureId and pp.del_flag = '1' " +
				" inner join supply s on sp.supplyId = s.id and s.del_flag = '1' " +
				" where sp.id = " + shopProductId + 
				" and sp.del_flag = '1' and sp.isValId = '01' " +
				" and s.user_id = '" + userId + "'" +
				" order by sp.id desc";
		List<ShopProductVo> shopProductVoList = jdbcTemplate.query(sql, new ShopProductVoMapper());
		if(shopProductVoList != null && shopProductVoList.size() > 0){
			return shopProductVoList.get(0);
		}
		return null;
	}
	/**
	 * @Description:产品列表数据转换用
	 */
	private static final class ShopProductVoMapper implements RowMapper<ShopProductVo> {
		public ShopProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductVo shopProductVo = new ShopProductVo();
			shopProductVo.setId(rs.getBigDecimal("id"));
			shopProductVo.setProductId(rs.getBigDecimal("productId"));
			shopProductVo.setShopId(rs.getString("shopId"));
			shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductVo.setSaleMode(rs.getString("saleMode"));
			shopProductVo.setProductName(rs.getString("productName"));
			shopProductVo.setProductPic(rs.getString("picturePath1"));
			
			return shopProductVo;
		}
	}
	/**根据 供货商产品id 返回ShopProductVO*/
	@Override
	public ShopProductVo getOneShopProductVO(String id) {
		String sql = "select * from shop_product sp where sp.id = ? ";
		Object[] params = { id };
		params = StringHelperTools.nullToString(params);
		
		logger.debug("查询单个shopproductvo:"+BaseUtil.logSQL(sql,params));
		
		ShopProductVo shopProductVo = new ShopProductVo();
		shopProductVo = getJdbcTemplate().queryForObject(sql,params,new RowMapper<ShopProductVo>() {
			public ShopProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ShopProductVo vo = new ShopProductVo();
				AutoInjection.Rs2Vo(rs, vo, null);
				return vo;
			}
		});
		return shopProductVo;
	}
	/**根据供货商产品信息提交产品审核申请*/
	@Override
	public int ApproveShopProductChange2(ShopProductApproveVo shopProductApproveVo) {

		String insertSQL = " insert into shop_product_approve ( " +
				"  id " +
				" ,supplyAreaId " +
				" ,priceBefore " +
				" ,priceAfter " +
				" ,stockBefore " +
				" ,stockAfter " +
				" ,saleStatus " +
				" ,approveStatus " +
				" ,expApproveTime " +
				" ,del_flag " +
				" ,add_date " +
				" ,add_user " +
				" ,remark " +
				" ) " +
				" values ( ? , ?, ?, ?, ?, ?, ?, '1', SYSDATE + 1, '1', SYSDATE, ?, ? )";

		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[9];
			parmas[0] = shopProductApproveVo.getId()[i];
			parmas[1] = shopProductAreaVo.getId();
			parmas[2] = shopProductAreaVo.getPrice();
			parmas[3] = shopProductAreaVo.getApprovePrice();
			parmas[4] = shopProductAreaVo.getStockNum();
			parmas[5] = shopProductAreaVo.getApproveStockNum();
			parmas[6] = shopProductAreaVo.getSaleStatus();
			parmas[7] = shopProductApproveVo.getShopProductVo().getAddUser();
			parmas[8] = shopProductAreaVo.getRemark();
			lstParams.add(parmas);
		}
		
		int[] i = jdbcTemplate.batchUpdate(insertSQL.toString(), lstParams);
		return i.length;
	}
	/**根据供货商产品信息更新供应商产品状态*/
	@Override
	public int UpdateShopProductStatus(ShopProductApproveVo shopProductApproveVo) {

		String updateSQL = " update supply_area0 set saleStatus = '1' where id = ? ";
		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[1];
			parmas[0] = shopProductAreaVo.getId();
			lstParams.add(parmas);
		}
		int[] i = jdbcTemplate.batchUpdate(updateSQL.toString(), lstParams);
		return i.length;
	}
	/**根据供货商产品信息更新供应商产品状态*/
	@Override
	public int UpdateShopBidProductStatus(ShopProductApproveVo shopProductApproveVo) {

		String updateSQL = " update supply_area0 set saleStatus = '1',createTime = sysdate where id = ? ";

		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (int i = 0; i < shopProductApproveVo.getLstShopProductAreaVo().size(); i++) {
			ShopProductAreaVo shopProductAreaVo = shopProductApproveVo.getLstShopProductAreaVo().get(i);
			Object[] parmas = new Object[1];
			parmas[0] = shopProductAreaVo.getId();
			lstParams.add(parmas);
		}
		int[] i = jdbcTemplate.batchUpdate(updateSQL.toString(), lstParams);
		return i.length;
	}
	
	@Override
	public Map<String, Object> QueryProductlist() {
		// TODO Auto-generated method stub
		String querySql="SELECT sortId,sortname FROM product_sort  WHERE parentId='0' and del_flag='1' and isvalid='01' and system_flag='01' ";
		Map<String,Object> ProductSort=new  HashMap<String,Object>();
		try{
		 SqlRowSet  sq= jdbcTemplate.queryForRowSet(querySql);
					
		  while(sq.next()){
			  
		    ProductSort.put( sq.getString("SORTNAME"), sq.getString("SORTID") );			
		  } 
    	}catch(DataAccessException e){
    	  e.printStackTrace();
    	  return null;
    	}
		return ProductSort;		
	}
	@Override
	public List<ProductSortVo> QueryProductSecondList(String parentsortId) {
		// TODO Auto-generated method stub
		String querySql="";
		if( !"".equals(parentsortId)){
		   querySql="SELECT sortId,sortname FROM product_sort  WHERE  del_flag='1' and isvalid='01' and parentid="+ Integer.parseInt( parentsortId );
		}else{
			
		   querySql="SELECT sortId,sortname FROM product_sort  WHERE  del_flag='1' and isvalid='01'";
		}
		
		List<ProductSortVo> productSortList=jdbcTemplate.query(querySql, new  RowMapper<ProductSortVo>(){

			@Override
			public ProductSortVo mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				// TODO Auto-generated method stub
				ProductSortVo vo=new ProductSortVo();
				AutoInjection.Rs2Vo(arg0, vo, null);
				return vo;
			}			
		});
		
		return productSortList;			
	}
	@Override
	public int updateSupplyArea0(ShopProductAreaApproveVo shopProductAreaApproveVo) {
        if (shopProductAreaApproveVo != null) {
            List<ShopProductAreaVo> shopProductAreaVoList = shopProductAreaApproveVo.getShopProductAreaVoList();
            if (shopProductAreaVoList != null && !shopProductAreaVoList.isEmpty()) {
                ShopProductAreaVo shopProductAreaVo = shopProductAreaVoList.get(0);
                StringBuffer sb = new StringBuffer();
                sb.append("update supply_area0 sa set");

                if (shopProductAreaVo.getGifts() != null && shopProductAreaVo.getGifts() != "") {

                    sb.append(" sa.gifts='" + shopProductAreaVo.getGifts() + "',");
                }
                if (shopProductAreaVo.getIscontinuous() != null && shopProductAreaVo.getIscontinuous() != "") {

                    sb.append(" sa.is_contain_invoice='" + shopProductAreaVo.getIscontinuous() + "'" + ",");

                }
                if (shopProductAreaVo.getApproveStockNum() > 0) {

                    sb.append(" sa.overplusnumber=" + shopProductAreaVo.getApproveStockNum() + ",");
                    sb.append(" sa.stocknumber=" + shopProductAreaVo.getApproveStockNum() + ",");

                }
                if (shopProductAreaVo.getApprovePrice() != null) {
                    sb.append(" sa.priceretailonline=" + shopProductAreaVo.getApprovePrice());
                }
                if (shopProductAreaVo.getSaleType().equals(TellingConstance.SALETYPE2)) {
                    sb.append(" where sa.SUPPLYPRODUCTID=" + shopProductAreaVo.getShopProductId());
                } else {
                    sb.append(" where sa.id=" + shopProductAreaVo.getSupplyAreaId());
                }
                logger.debug("上架商品信息修改："+sb.toString());
                return jdbcTemplate.update(sb.toString());
            }
            return 0;
        } else {
            return 0;
        }
		
	}
	@Override
	public String queryFor(long maijiaAreaId) {
		// TODO Auto-generated method stub
		String areaName="";
		StringBuffer sb=new StringBuffer();
		sb.append("with   eara as (select * from areainfo a start with a.areaid = ? connect by prior  parentid=areaid)");
        sb.append( " select areaname from eara where eara.arealevel=2") ;
		Object [] params =new Object[]{maijiaAreaId};		
		SqlRowSet sq =jdbcTemplate.queryForRowSet(sb.toString(), params);
		while(sq.next()){			  
			areaName=sq.getString("AREANAME");			
		 }	
		return areaName;		
	}
	
	@Override
	public int updateStatusByFronesupply(List<ShopProductAreaVo> shopProductAreaVo) {
		// TODO Auto-generated method stub
		
		String updateSQL = " update supply_area0 set Status = '2' where id = ? ";
		List<Object[]> lstParams = new ArrayList<Object[]>();
		for (ShopProductAreaVo  vo : shopProductAreaVo) {
			Object[] parmas = new Object[1];
			parmas[0] = vo.getSupplyAreaId();
			lstParams.add(parmas);
		}
		int[] i = jdbcTemplate.batchUpdate(updateSQL.toString(), lstParams);
		return i.length;

	}

	@Override
	public List<QuotationVo> searchForQuotation(String supplyId,String conditions, String productfirstSortId, String productSortid) {
		List<String> args = new ArrayList<String>();
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHH");
		SimpleDateFormat format1 = new SimpleDateFormat("yyyyMMdd");
		Calendar localDate=Calendar.getInstance();
		localDate.add(Calendar.DAY_OF_MONTH, -1);
		String str=format1.format(localDate.getTime());
		int m=localDate.get(Calendar.MINUTE);
		if(m>30){
			localDate.add(Calendar.HOUR_OF_DAY, 1);
		}
		String str1=format.format(localDate.getTime());
		StringBuilder sb = new StringBuilder();
		//创建productData表根据供应商ID获取上架产品
		sb.append("	with productData as (");
		sb.append(" select  pb.brandname,pm.productName,sa.customId,sa.priceRetailOnline,sa.id,sp.id shopproductid  ");
		sb.append(" from shop_product sp");
		sb.append(" inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '1' inner join product_brand pb on pb.brandid = pm.brandid");
		sb.append(" inner join supply s on sp.supplyId = s.id and s.del_flag = '1' inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '1' and sa.isValId = '01' ");
		sb.append(" left join shop_product_approve spa on sa.id = spa.supplyAreaId and spa.del_flag = '1' left join areaInfo ai on sa.areaId = ai.areaId");
		sb.append(" left join buyerInfo bi on sa.customId = bi.buyerId left join common_buyerinfo cb on sa.customId = cb.gbuyerid");
		sb.append(" where s.id = ? and sa.status = '1' and sa.saleStatus <> '1' and sp.del_flag = '1' and sp.isValId = '01'");
		args.add(supplyId);
		if(conditions!=null&&!conditions.equals("")){
			sb.append(" and lower(replace(pm.productName, ' ', '')) like ? ");
			args.add("%" + conditions.replaceAll("'", "''").replaceAll("\\s*", "").toLowerCase() + "%");
		}
		if(productfirstSortId!=null&&!productfirstSortId.equals("")){
			if(productSortid!=null&&!productSortid.equals("")){
				sb.append(" and pm.sortid =?");
				args.add(productSortid);
			}else{
				sb.append("	and pm.sortid in(select SORTID from product_sort where PARENTID=? and DEL_FLAG='1')");
				args.add(productfirstSortId);
			}
		}
		sb.append("),");
		//周均价
		sb.append(" weekPrice as(");
		sb.append(" select sa.id, avg(ap.averageprice) as weekAvgPrice from avg_price ap");
		sb.append(" inner join supply_area0 sa on ap.supply_area_id = sa.id and sa.isvalid = '01' and sa.del_flag = '1'");
		sb.append(" inner join shop_product sp on sa.supplyproductid = sp.id  and sp.isvalid = '01'  and sp.del_flag = '1'");
		sb.append(" inner join product_main pm  on sp.productid = pm.productid  and pm.del_flag = '1'");
		sb.append(" where trunc(ap.add_time) > trunc(sysdate) - 8  and sp.supplyid =? group by sa.id),");
		args.add(supplyId);
		//活动价
		sb.append(" active as(");
		sb.append(" select td.actprice, sa.id, tp.endtime,tp.l_endtime,tp.starttime,tp.l_starttime");
		sb.append(" from supply_area0 sa INNER JOIN shop_product sp on sp.id = sa.supplyproductid and sa.del_flag = '1' and sp.isvalid = '01'");
		sb.append(" inner join tm_PROMOTIOND td on td.shopproductid = sp.id INNER JOIN supply s on s.id = sp.supplyid and s.del_flag = '1'");
		sb.append(" inner join tm_PROMOTIONM tp on tp.id = td.pid and tp.publicity_time <= sysdate");
		sb.append(" and to_char(tp.endtime, 'yyyy/mm/dd') >= to_char(sysdate,'yyyy/mm/dd')");
		sb.append(" and to_char(tp.starttime, 'yyyy/mm/dd')<= to_char(sysdate,'yyyy/mm/dd')");
		sb.append(" and to_char(tp.l_endtime, 'HH24:MI:SS') >= to_char(sysdate,'hh24:mi:ss')");
		sb.append(" and to_char(tp.l_starttime, 'HH24:MI:SS') <= to_char(sysdate,'hh24:mi:ss')");
		sb.append(" and tp.status = '1'  and tp.supplyid = s.id),");
		//品牌销量
		sb.append(" brandSum as(");
		sb.append(" select pb.brandname, nvl(sum(od.product_num), 0) as brandSale from product_main pm");
		sb.append(" inner join shop_product sp on pm.productid = sp.productid  and sp.isvalid = '01'  and sp.del_flag = '1'");
		sb.append(" inner join product_brand pb   on pb.brandid = pm.brandid  and pb.isvalid = '01'  and pb.del_flag = '1'");
		sb.append(" inner join supply_area0 sa   on sa.supplyproductid = sp.id  and sa.isvalid = '01'  and sa.del_flag = '1'");
		sb.append(" inner join supply s   on s.id = sp.supplyid  and s.del_flag = '1'");
		sb.append(" left join order_detail0 od   on od.product_id = pm.productid");
		sb.append(" where sp.supplyid = ?  and sa.isvalid = '01' and sa.del_flag = '1' group by pb.brandname),");
		args.add(supplyId);
		//产品销量
		sb.append(" productSum as(");
		sb.append(" select pm.productname, nvl(sum(od.product_num), 0) as productSale from product_main pm");
		sb.append(" inner join shop_product sp  on pm.productid = sp.productid  and sp.isvalid = '01'  and sp.del_flag = '1'");
		sb.append(" inner join supply_area0 sa  on sa.supplyproductid = sp.id and sa.isvalid = '01'  and sa.del_flag = '1'");
		sb.append(" inner join supply s  on s.id = sp.supplyid and s.del_flag = '1'");
		sb.append(" left join order_detail0 od  on od.product_id = pm.productid");
		sb.append(" where sp.supplyid = ? and sa.isvalid = '01' and sa.del_flag = '1' group by pm.productname)");
		args.add(supplyId);
		//查询报价单信息并按品牌销量，产品销量排序
		sb.append(" select pd.brandname,pd.productname,nvl(a.actprice,pd.priceRetailOnline)  todayPrice,nvl(avg(sac.priceretailonline), ");
		sb.append(" nvl(a.actprice,pd.priceRetailOnline))  yesterdayPrice,nvl(w.weekAvgPrice,nvl(a.actprice,pd.priceRetailOnline)) weekAvgPrice ,bs.brandsale,ps.productsale");
		sb.append(" from productData pd");
		sb.append(" inner join brandSum bs on bs.brandname = pd.brandname");
		sb.append(" inner join productSum ps  on ps.productname = pd.productname");
		sb.append(" left join active a on a.id=pd.id left join weekPrice w on w.id=pd.id");
		sb.append("	left join supply_area0_"+str+"  sac on sac.id=pd.id and sac.statisticsdate=?");
		sb.append(" group by pd.brandname,pd.productname,nvl(a.actprice,pd.priceRetailOnline), nvl(w.weekAvgPrice,nvl(a.actprice,pd.priceRetailOnline)),bs.brandsale,ps.productsale ");
		sb.append(" order by bs.brandsale desc,ps.productsale desc");
		args.add(str1);
		logger.debug("查询品牌、产品名称、昨日价格、今日价格、一周均价:"+BaseUtil.logSQL(sb.toString(),args.toArray()));
		List<QuotationVo> list = jdbcTemplate.query(sb.toString(), args.toArray(), new RowMapper<QuotationVo>(){
			public QuotationVo mapRow(ResultSet rs, int i)
					throws SQLException {
				QuotationVo vo=new QuotationVo();
				vo.setBrandName(rs.getString("brandName"));
				vo.setProductName(rs.getString("productName"));
				vo.setTodayPrice(rs.getBigDecimal("todayPrice"));
				vo.setYesterdayPrice(rs.getBigDecimal("yesterdayPrice"));
				vo.setWeekAvgPrice(rs.getBigDecimal("weekAvgPrice"));
				return vo;
			}
		});
		return list;
	}
	@Override
	public boolean checkRelease(String supplyAreaId, String userid) {
		String sql="select count(1) from supply_area0 sa inner join shop_product sp on sp.id=sa.supplyproductid and sp.isvalid='01' and sp.del_flag='1'"
				+ "inner join supply s on s.id=sp.supplyid and s.del_flag='1' where sa.id=? and s.user_id=? and sa.isvalid='01' and sa.del_flag='1'";
		Object[] param=new Object[]{supplyAreaId,userid};
		logger.debug("检验供应商是否发布过该产品："+BaseUtil.logSQL(sql, param));
		return jdbcTemplate.queryForInt(sql,param)>0;
	}
	@Override
	public boolean updateSupplyArea(ShopProductAreaVo shopProductAreaVo) {
		StringBuffer sb = new StringBuffer();
        sb.append("update supply_area0 sa set");
        List<Object> list=new ArrayList<Object>();
        if (shopProductAreaVo.getGifts() != null && shopProductAreaVo.getGifts() != "") {
            sb.append(" sa.gifts=?,");
            list.add(shopProductAreaVo.getGifts());
        }
        if (shopProductAreaVo.getIscontinuous() != null && shopProductAreaVo.getIscontinuous() != "") {
            sb.append(" sa.is_contain_invoice=?,");
            list.add(shopProductAreaVo.getIscontinuous());
        }
        if (shopProductAreaVo.getApproveStockNum() > 0) {
            sb.append(" sa.overplusnumber=?,");
            sb.append(" sa.stocknumber=?,");
            list.add(shopProductAreaVo.getApproveStockNum());
            list.add(shopProductAreaVo.getApproveStockNum());
        }
        if (shopProductAreaVo.getApprovePrice() != null) {
            sb.append(" sa.priceretailonline=? ");
            list.add(shopProductAreaVo.getApprovePrice());
        }
        if (shopProductAreaVo.getSaleType().equals(TellingConstance.SALETYPE2)) {
            sb.append(" where sa.SUPPLYPRODUCTID=? ");
            list.add(shopProductAreaVo.getShopProductId());
        } else {
            sb.append(" where sa.id=?");
            list.add(shopProductAreaVo.getSupplyAreaId());
        }
        logger.debug("上架商品信息修改："+BaseUtil.logSQL(sb.toString(),list.toArray()));
        return jdbcTemplate.update(sb.toString(),list.toArray())>0;
	}
	@Override
	public boolean deleteSupplyArea(ShopProductAreaVo vo) {
		String sql="";
		if("2".equals(vo.getSaleType())){
			sql="update supply_area0 set isvalid='02',del_flag='0',onlyid=null where supplyproductid='"+vo.getShopProductId()+"'";
		}else{
			sql="update supply_area0 set isvalid='02',del_flag='0',onlyid=null where id='"+vo.getSupplyAreaId()+"'";
		}
        logger.debug("取消发布："+sql);
        int a=jdbcTemplate.update(sql);
        return a>0;
	}
	@Override
	public boolean returnStock(ShopProductAreaVo vo) {
		String sql="select nvl(sp.allocatable,0) from store_product sp where sp.pk_stordoc='"+vo.getPk_stordoc()+"' and sp.productid='"+vo.getProductId()+"'";
		logger.debug("查询商品库存："+sql);
		int count=jdbcTemplate.queryForInt(sql);
		String sql2="update store_product  set allocatable='"+(vo.getStockNumber().intValue()+count) +"' where pk_stordoc='"+vo.getPk_stordoc()+"' and productid='"+vo.getProductId()+"'";
		logger.debug("天联华建取消发布返还定向发布库存："+sql2);
		return jdbcTemplate.update(sql2)>0;
	}
}





